﻿CREATE PROCEDURE [acms].[UserSecurity_GetAllByEmailAddressPaged]
	@EmailAddress varchaR(100),
	@Sort varchar(255),
	@StartIndex int, 
	@PageSize int,
	@TotalRecords int output
AS

	if @sort is null or Len(@sort) = 0
		set @Sort = 'UserName'
		
	if @startIndex is null 
		set @startIndex = 0
		
	if @pageSize is null 
		set @pageSize = 10000000
	

	declare @tmp table (Id int, RowNumber int)
	
	declare @strSql varchar(2000)
	set @strSQl = '
	Insert @tmp
	SELECT Id, ROW_NUMBER() OVER (Order By ' + @Sort + ' as RowNumber 
	FROM [acms].[UserSecurity_View]
	WHERE EmailAddress = ''' + @EmailAddress + ''''
	
	EXEC sp_executesql @strSQl
	
	SELECT a.*
	FROM [acms].[UserSecurity_View] a
		inner join @tmp b on a.Id = b.Id
	Where 
		b.RowNumber >= @startIndex
		and b.RowNumber < @startIndex + @pageSize
	
	SELECT @TotalRecords = Count(Id)
	From @Tmp
	
RETURN 0;